package Controller;

import java.sql.DriverManager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;

import Models.Addition;
import Models.Ingredient;
import Models.Note;
import Models.Recipe;

import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;


public class DBportal {
	private Connection con = null;
	private Statement st = null;
	private ResultSet rs = null;
			
	public DBportal() {

        try {
            con = (Connection) DriverManager.getConnection(url, user, password);
            st = (Statement) con.prepareStatement("");
            rs = st.executeQuery("SELECT * FROM Recipe");


        } catch (SQLException ex) {
        	System.out.println("nope chuck testa");

        }
	}
	
	public void closeConnections() {
        try {

            if (con != null) {
                con.close();
            }
            if (rs != null) {
	        	
				rs.close();

            }
	        if (st != null) {
	            st.close();
	        }
        } catch (SQLException ex) {
        	System.out.println("Could not close connections");
        }
		
	}
	
	
	/*
	 * returns the id, name, style, and brewdate of each recipe in the database
	 */
	public Vector<Vector<String>> getAllRecipeTableEntries() {
		Vector<Vector<String>> table = new Vector<Vector<String>>();
		
        try {
			rs = st.executeQuery("SELECT idRecipe, name, style, brewDate FROM Recipe");
            int i = 1;
            int numCols = ((ResultSetMetaData) rs.getMetaData()).getColumnCount();
            while (rs.next()) {  //add each row to the table
        		Vector<String> row = new Vector<String>();
            	for(i = 1; i <= numCols; i++)
            		row.add(rs.getString(i));
            	table.add(row);
            }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return table;
	}

	/*
	 * Returns the requested recipe & populates it
	 */
	
	public Recipe getRecipe(String idRecipe) {
		Recipe rcp = new Recipe();
		//populate recipe class
        try {
			rs = st.executeQuery("SELECT * FROM Recipe WHERE idRecipe = " + idRecipe);
			rs.next();  //move to the row
			
			
			//////****SET ALL OF THE VALUES OF THE RECIPE*****//////////
			//I set them one at a time in case the database changes and the columns are returned
			//in a different order.
            rcp.setIdRecipe(rs.getString(DBcolNames.IDRECIPE.toString())); //set id
            
            rcp.setIdParent(rs.getString(DBcolNames.IDPARENT.toString()));            
            rcp.setName(rs.getString(DBcolNames.NAME.toString())); //set name
            rcp.setStyle(rs.getString(DBcolNames.STYLE.toString())); //set style
            rcp.setOG(rs.getString(DBcolNames.OG.toString()));//set OG float
            rcp.setFG(rs.getString(DBcolNames.FG.toString()));//set FG float
            rcp.setIBU(rs.getString(DBcolNames.IBU.toString()));  //SET IBU
            rcp.setPreBoilVol(rs.getString(DBcolNames.PREBOILVOL.toString()));//set Preboilvolume
            rcp.setPostBoilVol(rs.getString(DBcolNames.POSTBOILVOL.toString()));//set postboilvol        
            rcp.setBoilTime(rs.getString(DBcolNames.BOILTIME.toString()));  //set boil time int
            rcp.setMashTemp(rs.getString(DBcolNames.MASHTEMP.toString()));  //set mashTemp int
            rcp.setMashTime(rs.getString(DBcolNames.MASHTIME.toString()));  //set mashTime int
            rcp.setEfficiency(rs.getString(DBcolNames.EFFICIENCY.toString()));
            rcp.setBrewDate(rs.getString(DBcolNames.BREWDATE.toString()));

            rcp.setAddition(getAdditions(idRecipe));
            	
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rcp;
	}
	
	/*
	 * Generate an arraylist of all the Notes for a particular recipe
	 * The actual note is not returned unless the user wants it. call getNoteTXT to
	 * 	private int idNote = EMPTY_NOTE;
		private int idRecipe;
		private String stage;
	 * get the text contained in a note.
	 */
	public ArrayList<Note> getNotes(String idRecipe) {
		
	   ArrayList<Note> notes = new ArrayList<Note>();
       try {
			rs = st.executeQuery("SELECT " +DBcolNames.IDNOTE.toString() + ", " +
											DBcolNames.STAGE.toString() +", " +
											"WHERE idRecipe = " + idRecipe);
            while (rs.next()) {  //add each note to the arraylist
            	Note current = new Note();
            	current.setIdNote(rs.getString((DBcolNames.IDNOTE.toString())));
            	current.setIdRecipe(idRecipe);
            	current.setStage((String)rs.getObject(DBcolNames.STAGE.toString()));
            	notes.add(current);
            }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return notes;
	}
	
	/**
	 * returns an array list that contains all of the additions for a recipe
	 */
	private ArrayList<Addition> getAdditions(String idRecipe) {
		ArrayList<Addition> additions = new ArrayList<Addition>();
	       try {
				rs = st.executeQuery("SELECT Ingredient.ingredientName, additionMethod, " + 
										"weight, alpha, hopType, ingredientType " +
										"FROM Addition NATURAL JOIN Ingredient " +
										"WHERE idRecipe = " + idRecipe);
	            while (rs.next()) {  //add each note to the arraylist
	            	Addition current = new Addition();
	            	Ingredient currentIng = new Ingredient();
	            	current.setIdRecipe(idRecipe);
	            	current.setAdditionMethod(rs.getString(2));
	            	current.setWeight(rs.getString(3));
	            	current.setAlpha(rs.getString(4));
	            	current.setHopType(rs.getString(5));//only used for hops
	
	            	currentIng.setIngredientName(rs.getString(1));
	            	currentIng.setIngredientType(rs.getString(6));
	            	
	            	current.setTheIngredient(currentIng);
	            	additions.add(current);
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return additions;
	}
	
	/**
	 *  Returns an ArrayList containing all of a particular group of ingredients.
	 */
	public ArrayList<Ingredient> getIngredient(String type) {
		ArrayList<Ingredient>  ingredients = new ArrayList<Ingredient>();
		 try {
				rs = st.executeQuery("SELECT ingredientName, ppg, description" +
						" FROM Ingredient WHERE ingredientType = '" + type + "' ORDER BY ingredientName asc");
	            
				while (rs.next()) {  //add each note to the arraylist
	            	Ingredient ing = new Ingredient();
	            	ing.setIngredientName(rs.getString((DBcolNames.INGREDIENT_NAME.toString())));
	            	ing.setIngredientType(type);
	            	ing.setDescription(rs.getString((DBcolNames.DESCRIPTION.toString())));
	            	ingredients.add(ing);
	            }
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return ingredients;
	}/*
	private String ppg; //points per gallon
	
	private String ingredientName;
	private String ingredientType;
	private String description;
	*/
	//getfermentation
	//setrecipe
	//addnote
	//addfermentation
	//deletelastferm optional
	//deleterecipe
	//deletebrewferm
	//deletenote
	//removeingredient
	//addingredient
	//updateingredient
	//addaddition
	//updateaddition
	//removeaddition
	//getlistofingredientssd

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
   private String url = "jdbc:mysql://dustinstriplin.com/dustpan_Journal?allowMultiQueries=true";
   private String user = "dustpan_dustin";
   private String password = "asdf1234";
}
